﻿using System;
using System.Linq;
using MySql.Data.MySqlClient;
using System.Data;
using iRealtor.Maps.DataAccess.Models;

namespace iRealtor.Maps.DataAccess.Dao.EntityDataProviders
{
	public class PlacesDataProvider : EntityDataProvider<Place>
	{
		private const string INSERT_COMMAND = "INSERT INTO Places(Name, Description, Longitude, Latitude) VALUES(@Name, @Description, @Longitude, @Latitude); SELECT @@IDENTITY;";
		private const string UPDATE_COMMAND = "UPDATE Places SET Name=@Name, Description=@Description, Longitude=@Longitude, Latitude=@Latitude WHERE PlaceId=@PlaceId;";
		private const string SELECT_COMMAND = "SELECT * FROM Places WHERE PlaceId=@PlaceId;";

		public override void Save(Place entity, IMySqlCommandCreater cmdCreater)
		{
			MySqlCommand cmd = cmdCreater.CreateCommand();

			if (entity.PlaceId == 0)
			{
				cmd.CommandText = INSERT_COMMAND;
			}
			else
			{
				cmd.CommandText = UPDATE_COMMAND;
				cmd.Parameters.Add("@PlaceId", MySqlDbType.Int32).Value = entity.PlaceId;
			}

			cmd.Parameters.Add("@Name", MySqlDbType.String).Value = entity.Name;
			cmd.Parameters.Add("@Description", MySqlDbType.Text).Value = entity.Description;
			cmd.Parameters.Add("@Longitude", MySqlDbType.Double).Value = entity.Longitude;
			cmd.Parameters.Add("@Latitude", MySqlDbType.Double).Value = entity.Latitude;

			if (entity.PlaceId == 0)
			{
				entity.PlaceId = Convert.ToInt32(cmd.ExecuteScalar());
			}
			else
			{
				if (cmd.ExecuteNonQuery() == 0)
				{
					throw new Exception(string.Format("Place({0}) was not found in database.", entity.PlaceId));
				}
			}
		}

		public override Place Load(object key, IMySqlCommandCreater cmdCreater)
		{
			MySqlCommand cmd = cmdCreater.CreateCommand(SELECT_COMMAND);
			cmd.Parameters.Add("@PlaceId", MySqlDbType.Int32).Value = key;

			return Load(cmd).FirstOrDefault();
		}

		public override Place Load(DataRow row)
		{
			var place = new Place();

			if (!(row["PlaceId"] is DBNull))
				place.PlaceId = Convert.ToInt32(row["PlaceId"]);
			if (!(row["Name"] is DBNull))
				place.Name = Convert.ToString(row["Name"]);
			if (!(row["Description"] is DBNull))
				place.Description = Convert.ToString(row["Description"]);
			if (!(row["Longitude"] is DBNull))
				place.Longitude = Convert.ToDouble(row["Longitude"]);
			if (!(row["Latitude"] is DBNull))
				place.Latitude = Convert.ToDouble(row["Latitude"]);

			return place;
		}
	}
}
